Namespace persistent
    Public Class tariffcompare

        Shared Function getCommonSqlStringPart(ByVal msisdn As String) As String
            Dim sql As String = ""
            sql += " from (select /*+ index(eu_bill.tariff_comparison eu_bill.IDX_TARIFF_COMPARISON ) */ simulation_no,"
            sql += "        bc_period,"
            sql += "        billcycle,"
            sql += "        msisdn,"
            sql += "        cdr_destination,"
            sql += "        TARIFF_ID,"
            sql += "        TARIFF_DESC,"
            sql += "        SUM_DISCOUNTED_AMOUNT,        "
            sql += "        SUM_ORIGIN_DURATION,"
            sql += "        case  "
            sql += "               when tariff_type.rowno = 2 then TARIFF1 "
            sql += "               when tariff_type.rowno = 3 then TARIFF2"
            sql += "               when tariff_type.rowno = 4 then TARIFF3"
            sql += "               when tariff_type.rowno = 5 then TARIFF4"
            sql += "               when tariff_type.rowno = 6 then TARIFF5"
            sql += "               when tariff_type.rowno = 7 then TARIFF6"
            sql += "               when tariff_type.rowno = 8 then TARIFF7"
            sql += "               when tariff_type.rowno = 9 then TARIFF8"
            sql += "               when tariff_type.rowno = 10 then TARIFF9"
            sql += "               when tariff_type.rowno = 11 then TARIFF10"
            sql += "               when tariff_type.rowno = 12 then TARIFF11"
            sql += "        end tariff"
            sql += "        ,case "
            sql += "             when tariff_type.rowno = 2 then CTARIFF_DESC_1"
            sql += "             when tariff_type.rowno = 3 then CTARIFF_DESC_2"
            sql += "             when tariff_type.rowno = 4 then CTARIFF_DESC_3"
            sql += "             when tariff_type.rowno = 5 then CTARIFF_DESC_4"
            sql += "             when tariff_type.rowno = 6 then CTARIFF_DESC_5"
            sql += "             when tariff_type.rowno = 7 then CTARIFF_DESC_6"
            sql += "             when tariff_type.rowno = 8 then CTARIFF_DESC_7"
            sql += "             when tariff_type.rowno = 9 then CTARIFF_DESC_8"
            sql += "             when tariff_type.rowno = 10 then CTARIFF_DESC_9"
            sql += "             when tariff_type.rowno = 11 then CTARIFF_DESC_10"
            sql += "             when tariff_type.rowno = 12 then CTARIFF_DESC_11"
            sql += "         end ctariff_desc"
            sql += "        ,case  "
            sql += "             when tariff_type.rowno = 2 then TARIFF1_AMNT"
            sql += "             when tariff_type.rowno = 3 then TARIFF2_AMNT"
            sql += "             when tariff_type.rowno = 4 then TARIFF3_AMNT"
            sql += "             when tariff_type.rowno = 5 then TARIFF4_AMNT"
            sql += "             when tariff_type.rowno = 6 then TARIFF5_AMNT"
            sql += "             when tariff_type.rowno = 7 then TARIFF6_AMNT"
            sql += "             when tariff_type.rowno = 8 then TARIFF7_AMNT"
            sql += "             when tariff_type.rowno = 9 then TARIFF8_AMNT"
            sql += "             when tariff_type.rowno = 10 then TARIFF9_AMNT"
            sql += "             when tariff_type.rowno = 11 then TARIFF10_AMNT"
            sql += "             when tariff_type.rowno = 12 then TARIFF11_AMNT"
            sql += "         end tariff_amnt"
            sql += "                    from"
            sql += "    eu_bill.tariff_comparison,           "
            sql += "    ("
            sql += "      select rownum rowno"
            sql += "      from(dual)"
            sql += "      connect by level <= 13"
            sql += "     ) tariff_type"
            sql += "            where msisdn = '" + msisdn + "' "
            sql += "            and simulation_no in (1,3,4,5,6) "
            sql += "order by bc_period desc, cdr_destination) where tariff <> 0 and tariff <> (select tariff_id from eu_bill.tariff_comparison where msisdn = '" + msisdn + "' and rownum = 1)"

            Return sql
        End Function
        Public Shared Function getTariffsToCompare(ByVal msisdn As String) As ArrayList
            Dim dt As DataTable
            Dim sql As String
            Dim list As ArrayList = New ArrayList
            sql = " select distinct(CTARIFF_DESC) " + getCommonSqlStringPart(msisdn) + " and simulation_no = (select simulation_no " + getCommonSqlStringPart(msisdn) + " and rownum = 1)"

            dt = myDBConn.myORATable(sql)

            Dim ds As DataSet
            ds = dt.DataSet()
            Dim row As DataRow

            For Each row In ds.Tables(0).Rows
                list.Add(row(0).ToString())
            Next

            Return list
        End Function
        Shared Function getInvoiceData(ByVal msisdn As String, ByVal month As Integer) As DataSet
            Dim sql As String
            sql = "select  * "
            sql += "from(eu_bill.tariff_comparison)"
            sql += "where   msisdn = '" + msisdn + "' "
            sql += "and simulation_no in (1,3,4,5,6) "
            sql += "and     bc_period =  (select max(bc_period)-" + month.ToString
            sql += "                        from(eu_bill.tariff_comparison)"
            sql += "                        where   msisdn = '" + msisdn + "'"
            sql += "                        and     simulation_no in (1,3,4,5,6)"
            sql += "                     )"
            sql += " order by bc_period , tariff_id, cdr_destination"
            Dim dt As DataTable = myDBConn.myORATable(sql)
            Dim ds As DataSet = dt.DataSet()
            Return ds
        End Function
        Shared Function getNumberOfAvailablePastInvoice(ByVal msisdn As String) As Integer
            Dim sql As String
            sql = "select  max(bc_period) - min(bc_period) "
            sql += "from(eu_bill.tariff_comparison)"
            sql += "where   msisdn = '" + msisdn + "' "
            sql += "and simulation_no in (1,3,4,5,6) "
            Dim dt As DataTable = myDBConn.myORATable(sql)
            Dim o As Object = dt.DataSet().Tables(0).Rows(0).Item(0)
            If o Is DBNull.Value Then
                Return 0
            End If

            Return o
        End Function
    End Class
End Namespace
